const express = require('express');
const router = express.Router();
const query = require('../config/db');

// 同步课程-地区
router.get('/getArea', async (req, res, next) => {
    let sql = `select * from e_area`;
    let result = await query(sql);
    res.send({
        success: true,
        msg: '查询成功',
        status: 200,
        data: result
    });
});

// 同步课程-年级
router.get('/getGrade', async (req, res, next) => {
    let sql = `select * from e_grade`;
    let result = await query(sql);
    res.send({
        success: true,
        msg: '查询成功',
        status: 200,
        data: result
    });
});

// 同步课程-学科
router.get('/getSubject', async (req, res, next) => {
    let sql = `select * from e_subject`;
    let result = await query(sql);
    res.send({
        success: true,
        msg: '查询成功',
        status: 200,
        data: result
    });
});

// 同步课程-类别
router.get('/getCategory', async (req, res, next) => {
    let sql = `select * from e_category`;
    let result = await query(sql);
    res.send({
        success: true,
        msg: '查询成功',
        status: 200,
        data: result
    });
});

// 同步课程-课程
// 1. 分页
// 2. 筛选
router.get("/course", async (req, res, next) => {
    // 1.1(2.1) 接收前端传递过来的page和size area[地区]、grade[年级] 、subject[学科]、cate[类别]
    let { page = 1, size = 10, area = '', grade = '', subject = '', cate = '' } = req.query;

    // 2.2 where语句
    let whereStr = '';
    // 2.3 地区 
    // 先判断whereStr是否为空，如果是空，就是用where拼接；如果不是，就使用and拼接
    if (whereStr == '') {
        if (area !== '') {
            whereStr += `where area_name='${area}'`;
        }
    } else {
        if (area !== '') {
            whereStr += `and area_name='${area}'`;
        }
    };

    // 2.4 年级
    if (whereStr == '') {
        if (grade !== '') {
            whereStr += `where grade_name='${grade}'`;
        }
    } else {
        if (grade !== '') {
            whereStr += `and grade_name='${grade}'`;
        }
    };

    // 2.5 学科
    if (whereStr == '') {
        if (subject !== '') {
            whereStr += `where subject_name='${subject}'`;
        }
    } else {
        if (subject !== '') {
            whereStr += `and subject_name='${subject}'`;
        }
    };

    // 2.6 类别
    if (whereStr == '') {
        if (cate !== '') {
            whereStr += `where category_name='${cate}'`;
        }
    } else {
        if (cate !== '') {
            whereStr += `and category_name='${cate}'`;
        }
    };

    // 1.2 将page和size转换为number
    page = Number(page);
    size = Number(size);

    // 1.3 查询所有数据
    // let sql1 = `select * from e_course`;
    let sql1 = `select * from e_course ${whereStr}`;
    let result1 = await query(sql1);

    // 1.4 总页数
    let totalPage = Math.ceil(result1.length / size);

    // 1.5 总条数
    let totalNum = result1.length;

    // 1.6 第page页的size条数据 
    let sql2 = `SELECT * FROM e_course ${whereStr} LIMIT ${(page - 1) * size},${size}`;
    let result2 = await query(sql2);

    // 1.7返回给前端
    res.send({
        success: true,
        msg: "查询成功",
        status: 200,
        data: {
            totalPage,
            totalNum,
            // 数据
            list: result2,
            // 当前页码
            page
        }
    })
})

module.exports = router;